const db = require('../config/database');

class MomentService {
  sqlFragment = `select m.id as id, m.content as content, m.createAt as createAt, m.updateAt as updateAt,
  JSON_OBJECT('id',u.id,'username',u.username) as createUser   from moment as m
  left join users as u
  on m.user_id = u.id
 `;
  // 新增
  async create({ content, userId }) {
    const sql = `INSERT INTO moment(content,user_id) values(?,?)`;
    const result = await db.execute(sql, [content, userId]);
    console.log(result);
    return result[0];
  }

  async list({ pageSize, pageNumber }) {
    const sql = `
    select m.id as id ,m.content as content, m.createAt as creareAt, m.updateAt as updateAt,
    JSON_OBJECT('id',u.id,'username',u.username) as createUser,
    (select IF(COUNT(c.id),
      JSON_ARRAYAGG(
       JSON_OBJECT(
         'id', c.id,
         'content',c.content,
         'createAt', c.createAt,
         'updateAt',c.updateAt,
         'momentId',c.moment_id,
         'user', JSON_OBJECT('id',cu.id,'username',cu.username)
       )
     ),JSON_ARRAY())
     from comment as c
     LEFT JOIN users as cu on c.user_id = cu.id
     WHERE c.moment_id = m.id
    ) AS comments ,
    (
      SELECT IF(count(t.id),
      JSON_ARRAYAGG(
        JSON_OBJECT('id',t.id,'name',t.name)
      ) ,JSON_ARRAY())
      from moment_tag as mt
      LEFT JOIN tag as t on mt.tag_id = t.id
      WHERE mt.moment_id = m.id
    ) as tags
    from moment as m
    left join users as u on m.user_id = u.id
    limit ${(pageNumber - 1) * pageSize} , ${pageSize};
    `;
    const [result] = await db.execute(sql);
    return result;
  }

  // 查询
  async queryDetailById(id) {
    const sql = `
    select m.id as id, m.content as content, m.createAt as createAt, m.updateAt as updateAt,
    JSON_OBJECT('id',u.id,'username',u.username) as createUser,
    (select IF(COUNT(c.id),
     JSON_ARRAYAGG(
     JSON_OBJECT(
       'id', c.id,
       'content',c.content,
       'createAt', c.createAt,
       'updateAt',c.updateAt,
       'momentId',c.moment_id,
       'user', JSON_OBJECT('id',cu.id,'username',cu.username)
     )
   ),JSON_ARRAY())
   from comment as c
   LEFT JOIN users as cu on c.user_id = cu.id
   WHERE c.moment_id = m.id
  ) AS comments ,
    (
      SELECT IF(count(t.id),
      JSON_ARRAYAGG(
        JSON_OBJECT('id',t.id,'name',t.name)
      ) ,JSON_ARRAY())
      from moment_tag as mt
      LEFT JOIN tag as t on mt.tag_id = t.id
      WHERE mt.moment_id = m.id
    ) as tags
    from moment	as m
    left JOIN users as u on m.user_id = u.id
    left JOIN comment as c on  m.id = c.moment_id
    left JOIN users as cu on c.user_id = cu.id
    where m.id = ${id}
 `;
    const result = await db.execute(sql, []);
    return result[0];
  }

  // 更新
  async updateContentById(id, content) {
    const sql = `update moment set content = ? where id = ?`;
    const [result] = await db.execute(sql, [content, id]);
    return result;
  }

  // 删除
  async removeById(userId, id) {
    const sql = `delete from moment where id =? and user_id = ?`;
    const [result] = await db.execute(sql, [id, userId]);
    return result;
  }

  // 添加标签
  async addTag(momentId, tagList = []) {
    const sql = `insert ignore into moment_tag(moment_id,tag_id) values `;
    const tpl = tagList.reduce((prev, tagId) => {
      return prev + `,(${momentId},${tagId})`;
    }, '');
    const finalSql = sql + tpl.replace(',', '');
    const [result] = await db.execute(finalSql, []);
    return result;
  }
}

module.exports = new MomentService();
